package com.xliang.spider.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.xliang.db.DBUtil;

public class VideoUtil
{
    public static Video findVideoByName(String name)
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Video video = null;
        try
        {
            conn = DBUtil.open();
            String sql = "select * from video where video_name = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, name);
            rs = pstmt.executeQuery();
            if (rs.next())
            {
                video = new Video();
                video.setId(rs.getInt("id"));
                video.setIndex(rs.getInt("video_index"));
                video.setName(rs.getString("video_name"));
                video.setUrl(rs.getString("video_url"));
                video.setStatus(rs.getInt("status"));
            }
        }
        catch (Exception e)
        {
            System.err.println(String.format("查找视频【%s】出错，错误消息：%s", name, e.getMessage()));
            e.printStackTrace();
        }
        finally
        {
            DBUtil.close(rs);
            DBUtil.close(pstmt);
            DBUtil.close(conn);
        }
        return video;
    }
    
    public static boolean updateVideo(Video video)
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int result = 0;
        try
        {
            conn = DBUtil.open();
            String sql = "update video set video_index=?,video_url=?,status=? where video_name=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, video.getIndex());
            pstmt.setString(2, video.getUrl());
            pstmt.setInt(3, video.getStatus());
            pstmt.setString(4, video.getName());
            result = pstmt.executeUpdate();
        }
        catch (Exception e)
        {
            System.err.println(String.format("更新视频【%s】出错,错误消息：%s", video.getName(), e.getMessage()));
            e.printStackTrace();
        }
        finally
        {
            DBUtil.close(pstmt);
            DBUtil.close(conn);
        }
        return result == 1;
    }
    
    public static int addVideo(Video video)
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int result = 0;
        try
        {
            conn = DBUtil.open();
            String sql = "insert into video(video_index,video_name,video_url) values(?,?,?)";
            pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            pstmt.setInt(1, video.getIndex());
            pstmt.setString(2, video.getName());
            pstmt.setString(3, video.getUrl());
            result = pstmt.executeUpdate();
        }
        catch (Exception e)
        {
            if (e.getMessage().contains("uniq_video_name") && e.getMessage().contains("Duplicate"))
            {
                Video old = findVideoByName(video.getName());
                if (old.getId() != old.getIndex() || old.getStatus() == 2)
                {
                    old.setIndex(video.getIndex());
                    if (old.getStatus() == 2)
                    {
                        old.setUrl(video.getUrl());
                        old.setStatus(0);
                    }
                    updateVideo(old);
                }
                else
                {
                    System.out.println(String.format("视频【%s】已经存在，跳过。。。", video.getName()));
                }
            }
            else
            {
                System.err.println(String.format("新增视频【%s】出错,错误消息：%s", video.getName(), e.getMessage()));
            }
        }
        finally
        {
            DBUtil.close(pstmt);
            DBUtil.close(conn);
        }
        return result;
    }
    
    public static List<Video> findUnDownloadVideo()
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Video> videos = new ArrayList<>();
        try
        {
            conn = DBUtil.open();
            String sql = "select * from video where status = 0";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next())
            {
                Video video = new Video();
                video.setId(rs.getInt("id"));
                video.setIndex(rs.getInt("video_index"));
                video.setName(rs.getString("video_name"));
                video.setUrl(rs.getString("video_url"));
                video.setStatus(rs.getInt("status"));
                videos.add(video);
            }
        }
        catch (Exception e)
        {
            System.err.println(String.format("查找未下载视频出错，错误消息：%s", e.getMessage()));
            e.printStackTrace();
        }
        finally
        {
            DBUtil.close(rs);
            DBUtil.close(pstmt);
            DBUtil.close(conn);
        }
        return videos;
    }
}
